﻿using System;
using System.Collections;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Configuration.Provider;
using System.Globalization;
using System.Data;
using System.Security.Permissions;
using System.Web.UI.WebControls.WebParts;
using System.Web.Security;
using System.Web.DataAccess;
using System.Web.Util;
using Reviewer.Security.Helpers;
using MySql.Data.MySqlClient;
using System.Configuration;

namespace Reviewer.Security
{
    /// <devdoc>
    /// The provider used to access the personalization store for WebPart pages from an $safeprojectname$
    /// database.
    /// </devdoc>
    public sealed class ReviewerPersonalizationProvider : PersonalizationProvider
    {
        #region fields
        private const int MaxStringLength = 255;
        private string applicationName;
        private string connectionString;
        #endregion

        /// <summary>
        /// Initializes a new instance of the <see cref="ReviewerPersonalizationProvider"/> class.
        /// </summary>
        /// <devdoc>
        /// Initializes an instance of AccessPersonalizationProvider.
        /// </devdoc>
        public ReviewerPersonalizationProvider()
        {
        }

        /// <summary>
        /// When overridden in a derived class, gets or sets the name of the application configured for the provider.
        /// </summary>
        /// <value></value>
        /// <returns>
        /// The application configured for the personalization provider.
        /// </returns>
        public override string ApplicationName
        {
            get
            {
                if (String.IsNullOrEmpty(this.applicationName))
                {
                    this.applicationName = SecUtility.GetDefaultAppName();
                }

                return this.applicationName;
            }
            set
            {
                if (value != null && value.Length > MaxStringLength)
                {
                    throw new ProviderException("Application name exceeded max length of " + MaxStringLength);
                }
                applicationName = value;
            }
        }

        /// <summary>
        /// Initializes the specified name.
        /// </summary>
        /// <param name="name">The name.</param>
        /// <param name="configSettings">The config settings.</param>
        public override void Initialize(string name, NameValueCollection config)
        {
            base.Initialize(name, config);
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }

            // If not available, the default value is set in the get accessor of ApplicationName
            this.applicationName = config["applicationName"];
            if (this.applicationName != null)
            {
                config.Remove("applicationName");

                if (this.applicationName.Length > MaxStringLength)
                {
                    throw new ProviderException("Application name exceeded max length of " + MaxStringLength);
                }
            }

            string connectionStringName = config["connectionStringName"];

            if (String.IsNullOrEmpty(connectionStringName))
            {
                throw new ProviderException("No connection string specified.");
            }
            config.Remove("connectionStringName");

            string temp = ConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString;
            if (String.IsNullOrEmpty(temp))
            {
                throw new ProviderException("Bad connection string: " + connectionStringName);
            }

            this.connectionString = temp;

            if (config.Count > 0)
            {
                string invalidAttributeName = config.GetKey(0);

                throw new ProviderException("Unknown attribute: " + invalidAttributeName + name);
            }
        }

        /// <summary>
        /// When overridden in a derived class, returns a collection containing zero or more <see cref="T:System.Web.UI.WebControls.WebParts.PersonalizationStateInfo"/>-derived objects based on scope and specific query parameters.
        /// </summary>
        /// <param name="scope">A <see cref="T:System.Web.UI.WebControls.WebParts.PersonalizationScope"/> with the personalization information to be queried. This value cannot be null.</param>
        /// <param name="query">A <see cref="T:System.Web.UI.WebControls.WebParts.PersonalizationStateQuery"/> containing a query. This value can be null.</param>
        /// <param name="pageIndex">The location where the query starts.</param>
        /// <param name="pageSize">The number of records to return.</param>
        /// <param name="totalRecords">The total number of records available.</param>
        /// <returns>
        /// A <see cref="T:System.Web.UI.WebControls.WebParts.PersonalizationStateInfoCollection"/> containing zero or more <see cref="T:System.Web.UI.WebControls.WebParts.PersonalizationStateInfo"/>-derived objects.
        /// </returns>
        public override PersonalizationStateInfoCollection FindState(PersonalizationScope scope, PersonalizationStateQuery query, int pageIndex, int pageSize, out int totalRecords)
        {
            PersonalizationProviderHelper.CheckPersonalizationScope(scope);
            PersonalizationProviderHelper.CheckPageIndexAndSize(pageIndex, pageSize);

            if (scope == PersonalizationScope.Shared)
            {
                string pathToMatch = null;
                if (query != null)
                {
                    pathToMatch = PersonalizationProviderHelper.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, MaxStringLength);
                }

                return FindSharedState(pathToMatch, pageIndex, pageSize, out totalRecords);
            }
            else
            {
                string pathToMatch = null;
                DateTime inactiveSinceDate = DateTime.MinValue;
                string usernameToMatch = null;
                if (query != null)
                {
                    pathToMatch = PersonalizationProviderHelper.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, MaxStringLength);
                    inactiveSinceDate = query.UserInactiveSinceDate;
                    usernameToMatch = PersonalizationProviderHelper.CheckAndTrimString(query.UsernameToMatch, "query.UsernameToMatch", false, MaxStringLength);
                }

                return FindUserState(pathToMatch, inactiveSinceDate, usernameToMatch, pageIndex, pageSize, out totalRecords);
            }
        }

        /// <summary>
        /// When overridden in a derived class, returns the number of rows in the underlying data store that exist within the specified scope.
        /// </summary>
        /// <param name="scope">A <see cref="T:System.Web.UI.WebControls.WebParts.PersonalizationScope"/> of the personalization information to be queried. This value cannot be null.</param>
        /// <param name="query">A <see cref="T:System.Web.UI.WebControls.WebParts.PersonalizationStateQuery"/> containing a query. This value can be null.</param>
        /// <returns>
        /// The number of rows in the underlying data store that exist for the specified <paramref name="scope"/> parameter.
        /// </returns>
        public override int GetCountOfState(PersonalizationScope scope, PersonalizationStateQuery query)
        {
            PersonalizationProviderHelper.CheckPersonalizationScope(scope);
            if (scope == PersonalizationScope.Shared)
            {
                string pathToMatch = null;
                if (query != null)
                {
                    pathToMatch = PersonalizationProviderHelper.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, MaxStringLength);
                }

                return GetCountOfSharedState(pathToMatch);
            }
            else
            {
                string pathToMatch = null;
                DateTime userInactiveSinceDate = DateTime.MinValue;
                string usernameToMatch = null;
                if (query != null)
                {
                    pathToMatch = PersonalizationProviderHelper.CheckAndTrimString(query.PathToMatch, "query.PathToMatch", false, MaxStringLength);
                    userInactiveSinceDate = query.UserInactiveSinceDate;
                    usernameToMatch = PersonalizationProviderHelper.CheckAndTrimString(query.UsernameToMatch, "query.UsernameToMatch", false, MaxStringLength);
                }

                return GetCountOfUserState(pathToMatch, userInactiveSinceDate, usernameToMatch);
            }
        }

        /// <summary>
        /// When overridden in a derived class, deletes Web Parts personalization data from the underlying data store based on the specified parameters.
        /// </summary>
        /// <param name="path">The path of the personalization data to be deleted. This value can be null but cannot be an empty string ("").</param>
        /// <param name="userInactiveSinceDate">The date indicating the last time a Web site user changed personalization data.</param>
        /// <returns>
        /// The number of rows deleted from the underlying data store.
        /// </returns>
        public override int ResetUserState(string path, DateTime userInactiveSinceDate)
        {
            path = PersonalizationProviderHelper.CheckAndTrimString(path, "path", false, MaxStringLength);

            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            int count = 0;


            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.connectionInstance;

                    // Special note: MySqlProvider requires the parameters to be added
                    // in the same order as appearing in the query text.

                    string getDeleteUserStateCount =
                        "SELECT COUNT(*)" +
                        " FROM AN_PagePersonalizationPerUser PerUser, AN_User Users, AN_Path Paths" +
                        " WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
                        " AND Users.LastActivityDate <= @InactiveSinceDate";

                    string deleteUserState =
                        "DELETE FROM AN_PagePersonalizationPerUser" +
                        " WHERE Id IN (SELECT PerUser.Id " +
                                     " FROM AN_PagePersonalizationPerUser PerUser, AN_User Users, AN_Path Paths" +
                                     " WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId" +
                                     " AND Users.LastActivityDate <= @InactiveSinceDate";

                    // Get the count of records that would be deleted
                    using (MySqlCommand command = new MySqlCommand())
                    {
                        command.Connection = connection;
                        MySqlParameterCollection parameters = command.Parameters;
                        MySqlParameter parameter;

                        // Note: MySql provider does not handle datetime that has non-
                        // zero millisecond, so it needs to be rounded up.
                        parameter = parameters.Add("InactiveSinceDate", MySqlDbType.DateTime);
                        parameter.Value = new DateTime(userInactiveSinceDate.Year, userInactiveSinceDate.Month, userInactiveSinceDate.Day,
                                                       userInactiveSinceDate.Hour, userInactiveSinceDate.Minute, userInactiveSinceDate.Second);

                        if (path != null)
                        {
                            const string pathParamQueryText = " AND Paths.Path = @Path";
                            getDeleteUserStateCount += pathParamQueryText;
                            deleteUserState += pathParamQueryText;
                            parameters.AddWithValue("Path", path);
                        }
                        deleteUserState += ")";

                        command.CommandText = getDeleteUserStateCount;

                        object lookupResult = command.ExecuteScalar();
                        if ((lookupResult != null) && (lookupResult is Int32))
                        {
                            count = (Int32)lookupResult;
                            if (count > 0)
                            {
                                // Do the actual deletion
                                command.CommandText = deleteUserState;
                                command.ExecuteNonQuery();
                            }
                        }
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return count;
        }

        /// <summary>
        /// When overridden in a derived class, deletes personalization state from the underlying data store based on the specified parameters.
        /// </summary>
        /// <param name="scope">A <see cref="T:System.Web.UI.WebControls.WebParts.PersonalizationScope"/> of the personalization information to be reset. This value cannot be null.</param>
        /// <param name="paths">The paths for personalization information to be deleted.</param>
        /// <param name="usernames">The user names for personalization information to be deleted.</param>
        /// <returns>The number of rows deleted.</returns>
        public override int ResetState(PersonalizationScope scope, string[] paths, string[] usernames)
        {
            PersonalizationProviderHelper.CheckPersonalizationScope(scope);
            paths = PersonalizationProviderHelper.CheckAndTrimNonEmptyStringEntries(paths, "paths", false, false, MaxStringLength);
            usernames = PersonalizationProviderHelper.CheckAndTrimNonEmptyStringEntries(usernames, "usernames", false, true, MaxStringLength);

            if (scope == PersonalizationScope.Shared)
            {
                PersonalizationProviderHelper.CheckUsernamesInSharedScope(usernames);
                return ResetSharedState(paths);
            }
            else
            {
                PersonalizationProviderHelper.CheckOnlyOnePathWithUsers(paths, usernames);
                return ResetUserState(paths, usernames);
            }
        }

        /// <summary>
        /// When overridden in a derived class, loads raw personalization data from the underlying data store.
        /// </summary>
        /// <param name="webPartManager">The <see cref="T:System.Web.UI.WebControls.WebParts.WebPartManager"/> managing the personalization data.</param>
        /// <param name="path">The path for personalization information to be used as the retrieval key.</param>
        /// <param name="userName">The user name for personalization information to be used as the retrieval key.</param>
        /// <param name="sharedDataBlob">The returned data for the <see cref="F:System.Web.UI.WebControls.WebParts.PersonalizationScope.Shared"/> scope.</param>
        /// <param name="userDataBlob">The returned data for the <see cref="F:System.Web.UI.WebControls.WebParts.PersonalizationScope.User"/> scope.</param>
        protected override void LoadPersonalizationBlobs(WebPartManager webPartManager, string path, string userName, ref byte[] sharedDataBlob, ref byte[] userDataBlob)
        {
            sharedDataBlob = null;
            userDataBlob = null;

            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;

            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.connectionInstance;

                    int pathID = MySqlConnectionHelper.GetPathID(connection, path);
                    if (pathID != 0)
                    {
                        string sharedDataValue = LoadPersonalizationBlob(connection, pathID);
                        sharedDataBlob = Deserialize(sharedDataValue);

                        if (userName != null)
                        {
                            int userID = MySqlConnectionHelper.GetUserID(connection, userName);
                            if (userID != 0)
                            {
                                string userDataValue = LoadPersonalizationBlob(connection, pathID, userID);
                                userDataBlob = Deserialize(userDataValue);
                            }
                        }
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// When overridden in a derived class, deletes raw personalization data from the underlying data store.
        /// </summary>
        /// <param name="webPartManager">The <see cref="T:System.Web.UI.WebControls.WebParts.WebPartManager"/> managing the personalization data.</param>
        /// <param name="path">The path for personalization information to be used as the data store key.</param>
        /// <param name="userName">The user name for personalization information to be used as the data store key.</param>
        protected override void ResetPersonalizationBlob(WebPartManager webPartManager, string path, string userName)
        {
            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;

            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.connectionInstance;

                    int pathID = MySqlConnectionHelper.GetPathID(connection, path);

                    if (pathID != 0)
                    {
                        if (String.IsNullOrEmpty(userName))
                        {
                            ResetPersonalizationBlob(connection, pathID);
                        }
                        else
                        {
                            int userID = MySqlConnectionHelper.GetUserID(connection, userName);
                            if (userID != 0)
                            {
                                ResetPersonalizationBlob(connection, pathID, userID);
                            }
                        }
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// When overridden in a derived class, saves raw personalization data to the underlying data store.
        /// </summary>
        /// <param name="webPartManager">The <see cref="T:System.Web.UI.WebControls.WebParts.WebPartManager"/> managing the personalization data.</param>
        /// <param name="path">The path for personalization information to be used as the data store key.</param>
        /// <param name="userName">The user name for personalization information to be used as the key.</param>
        /// <param name="dataBlob">The byte array of data to be saved.</param>
        protected override void SavePersonalizationBlob(WebPartManager webPartManager, string path, string userName, byte[] dataBlob)
        {
            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;


            try
            {
                try
                {
                    string blobValue = Serialize(dataBlob);

                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.connectionInstance;

                    int pathID = MySqlConnectionHelper.GetPathID(connection, path, true);

                    if (pathID != 0)
                    {
                        if (String.IsNullOrEmpty(userName))
                        {
                            SavePersonalizationBlob(connection, pathID, blobValue);
                        }
                        else
                        {
                            int userID = MySqlConnectionHelper.GetUserID(connection, userName, true);
                            if (userID != 0)
                            {
                                SavePersonalizationBlob(connection, pathID, userID, blobValue);
                            }
                        }
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        private static void ResetPersonalizationBlob(MySqlConnection connection, int pathID)
        {
            using (MySqlCommand lookupCommand = new MySqlCommand("DELETE FROM AN_PagePersonalizationAllUsers WHERE PathId = @PathId", connection))
            {

                lookupCommand.Parameters.Add(new MySqlParameter("@PathId", pathID));
                lookupCommand.ExecuteNonQuery();
            }
        }

        private static void ResetPersonalizationBlob(MySqlConnection connection, int pathID, int userID)
        {
            using (MySqlCommand lookupCommand = new MySqlCommand("DELETE FROM AN_PagePersonalizationPerUser WHERE PathId = @PathId AND UserId = @UserId", connection))
            {

                lookupCommand.Parameters.Add(new MySqlParameter("@PathId", pathID));
                lookupCommand.Parameters.Add(new MySqlParameter("@UserId", userID));
                lookupCommand.ExecuteNonQuery();
            }
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        private int ResetAllState(string getStateCountQuery, string deleteStateQuery)
        {
            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            int count = 0;


            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.connectionInstance;

                    // Get the count of records that would be deleted
                    using (MySqlCommand command = new MySqlCommand(getStateCountQuery, connection))
                    {
                        object lookupResult = command.ExecuteScalar();
                        if ((lookupResult != null) && (lookupResult is Int32))
                        {
                            count = (Int32)lookupResult;
                        }

                        // Do the actual deletion
                        command.CommandText = deleteStateQuery;
                        command.ExecuteNonQuery();
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return count;
        }

        private int ResetSharedState(string[] paths)
        {
            if (paths == null)
            {
                const string deleteAllSharedState = "DELETE FROM AN_PagePersonalizationAllUsers";
                const string getAllSharedStateCount = "SELECT COUNT(*) FROM AN_PagePersonalizationAllUsers";
                return ResetAllState(getAllSharedStateCount, deleteAllSharedState);
            }
            else
            {
                return ResetStatePerPaths("AN_PagePersonalizationAllUsers", paths);
            }
        }

        private int ResetUserState(string[] paths, string[] usernames)
        {
            int count = 0;
            bool hasPaths = !(paths == null || paths.Length == 0);
            bool hasUsernames = !(usernames == null || usernames.Length == 0);

            if (!hasPaths && !hasUsernames)
            {
                const string deleteAllUserState = "DELETE FROM AN_PagePersonalizationPerUser";
                const string getAllUserStateCount = "SELECT COUNT(*) FROM AN_PagePersonalizationPerUser";
                count = ResetAllState(getAllUserStateCount, deleteAllUserState);
            }
            else if (!hasUsernames)
            {
                count = ResetStatePerPaths("AN_PagePersonalizationPerUser", paths);
            }
            else
            {
                string path = (paths != null) ? paths[0] : null;
                count = ResetUserStatePerUsers(path, usernames);
            }

            return count;
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities"), System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private int ResetStatePerPaths(string tableName, string[] paths)
        {
            if (paths == null || paths.Length == 0)
            {
                return 0;
            }

            int count = 0;
            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;

            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.connectionInstance;

                    string fromAndWhereClause = " FROM " + tableName + " WHERE PathId = @PathId";
                    string selectCommandText = "SELECT COUNT(*)" + fromAndWhereClause;
                    string deleteCommandText = "DELETE" + fromAndWhereClause;
                    using (MySqlCommand command = new MySqlCommand(null, connection))
                    {
                        MySqlParameter pathParam = command.Parameters.Add(new MySqlParameter("@PathId", MySqlDbType.Int32));

                        foreach (string path in paths)
                        {
                            command.CommandText = selectCommandText;
                            pathParam.Value = MySqlConnectionHelper.GetPathID(connection, path);
                            int numOfRecords = (int)command.ExecuteScalar();
                            if (numOfRecords > 0)
                            {
                                command.CommandText = deleteCommandText;
                                command.ExecuteNonQuery();
                                count += numOfRecords;
                            }
                        }

                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return count;
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1031:DoNotCatchGeneralExceptionTypes")]
        private int ResetUserStatePerUsers(string path, string[] usernames)
        {
            int count = 0;
            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;

            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.connectionInstance;

                    using (MySqlCommand command = new MySqlCommand(null, connection))
                    {
                        MySqlParameter userIdParam = command.Parameters.Add(new MySqlParameter("@UserId", MySqlDbType.Int32));
                        string fromAndWhereClause = " FROM AN_PagePersonalizationPerUser WHERE UserId = @UserId";
                        if (!String.IsNullOrEmpty(path))
                        {
                            int pathId = MySqlConnectionHelper.GetPathID(connection, path);
                            fromAndWhereClause += " AND PathId = @PathId";
                            command.Parameters.Add(new MySqlParameter("@PathId", pathId));
                        }

                        string selectCommandText = "SELECT COUNT(*)" + fromAndWhereClause;
                        string deleteCommandText = "DELETE" + fromAndWhereClause;

                        foreach (string username in usernames)
                        {
                            command.CommandText = selectCommandText;
                            userIdParam.Value = MySqlConnectionHelper.GetUserID(connection, username);
                            int numOfRecords = (int)command.ExecuteScalar();
                            if (numOfRecords > 0)
                            {
                                command.CommandText = deleteCommandText;
                                command.ExecuteNonQuery();
                                count += numOfRecords;
                            }
                        }
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return count;
        }

        private static void SavePersonalizationBlob(MySqlConnection connection, int pathID, string state)
        {
            string currentDate = DateTime.Now.ToString(CultureInfo.InvariantCulture);
            using (MySqlCommand updateCommand = new MySqlCommand("UPDATE AN_PagePersonalizationAllUsers SET PageSettings = @PageSettings, LastUpdatedDate = @UpdatedDate WHERE PathId = @PathId", connection))
            {

                updateCommand.Parameters.Add(new MySqlParameter("@PageSettings", state));
                updateCommand.Parameters.Add(new MySqlParameter("@UpdatedDate", currentDate));
                updateCommand.Parameters.Add(new MySqlParameter("@PathId", pathID));
                if (updateCommand.ExecuteNonQuery() != 1)
                {
                    using (MySqlCommand insertCommand = new MySqlCommand("INSERT INTO AN_PagePersonalizationAllUsers (PathId, PageSettings, LastUpdatedDate) VALUES (@PathId, @PageSettings, @UpdatedDate)", connection))
                    {

                        insertCommand.Parameters.Add(new MySqlParameter("@PathId", pathID));
                        insertCommand.Parameters.Add(new MySqlParameter("@PageSettings", state));
                        insertCommand.Parameters.Add(new MySqlParameter("@UpdatedDate", currentDate));
                        insertCommand.ExecuteNonQuery();
                    }
                }
            }
        }

        private static void SavePersonalizationBlob(MySqlConnection connection, int pathID, int userID, string state)
        {
            DateTime now = DateTime.Now;

            using (MySqlCommand updateCommand = new MySqlCommand(@"UPDATE  AN_User " +
                                                          @"SET     LastActivityDate = @LastActivityDate " +
                                                          @"WHERE   UserId = @UserId", connection))
            {
                updateCommand.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", now));
                updateCommand.Parameters.Add(new MySqlParameter("@UserId", userID));
                updateCommand.ExecuteNonQuery();
            }

            string currentDate = now.ToString(CultureInfo.InvariantCulture);
            using (MySqlCommand updateCommand = new MySqlCommand("UPDATE AN_PagePersonalizationPerUser SET PageSettings = @PageSettings, LastUpdatedDate = @UpdatedDate WHERE PathId = @PathId AND UserId = @UserId", connection))
            {
                updateCommand.Parameters.Add(new MySqlParameter("@PageSettings", state));
                updateCommand.Parameters.Add(new MySqlParameter("@UpdatedDate", currentDate));
                updateCommand.Parameters.Add(new MySqlParameter("@PathId", pathID));
                updateCommand.Parameters.Add(new MySqlParameter("@UserId", userID));
                if (updateCommand.ExecuteNonQuery() != 1)
                {
                    using (MySqlCommand insertCommand = new MySqlCommand("INSERT INTO AN_PagePersonalizationPerUser (PathId, UserId, PageSettings, LastUpdatedDate) VALUES (@PathId, @UserId, @PageSettings, @UpdatedDate)", connection))
                    {

                        insertCommand.Parameters.Add(new MySqlParameter("@PathId", pathID));
                        insertCommand.Parameters.Add(new MySqlParameter("@UserId", userID));
                        insertCommand.Parameters.Add(new MySqlParameter("@PageSettings", state));
                        insertCommand.Parameters.Add(new MySqlParameter("@UpdatedDate", currentDate));
                        insertCommand.ExecuteNonQuery();
                    }
                }
            }
        }

        private static string Serialize(byte[] data)
        {
            if ((data == null) || (data.Length == 0))
            {
                return String.Empty;
            }
            return Convert.ToBase64String(data);
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1822:MarkMembersAsStatic")]
        private byte[] Deserialize(string data)
        {
            if (String.IsNullOrEmpty(data))
            {
                return null;
            }
            return Convert.FromBase64String(data);
        }

        //private MySqlParameter CreateDateTimeMySqlParameter(string parameterName, DateTime dt)
        //{
        //    MySqlParameter p = new MySqlParameter(parameterName, MySqlDbType.DateTime);
        //    p.Direction = ParameterDirection.Input;
        //    p.Value = MySqlConnectionHelper.RoundToSeconds(dt);
        //    return p;
        //}

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        private PersonalizationStateInfoCollection FindSharedState(string path, int pageIndex, int pageSize, out int totalRecords)
        {
            const string findSharedState =
                "SELECT Paths.Path, AllUsers.LastUpdatedDate, LEN(AllUsers.PageSettings)" +
                " FROM AN_PagePersonalizationAllUsers AllUsers, AN_Path Paths" +
                " WHERE AllUsers.PathId = Paths.PathId AND Paths.ApplicationId = @ApplicationId";
            const string orderBy = " ORDER BY Paths.Path ASC";
            const string findUserState =
                "SELECT SUM(LEN(PerUser.PageSettings)), COUNT(*)" +
                " FROM AN_PagePersonalizationPerUser PerUser, AN_Path Paths" +
                " WHERE PerUser.PathId = Paths.PathId" +
                " AND Paths.ApplicationId = @ApplicationId" +
                " AND Paths.Path LIKE @Path";

            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            MySqlDataReader reader = null;
            totalRecords = 0;

            try
            {
                try
                {
                    MySqlParameterCollection parameters = null;
                    MySqlParameter parameter;

                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.connectionInstance;
                    using (MySqlCommand command = new MySqlCommand(findSharedState, connection))
                    {
                        parameters = command.Parameters;


                        if (path != null)
                        {
                            command.CommandText += " AND Paths.Path LIKE @Path";
                            parameter = parameters.Add("Path", MySqlDbType.VarChar);
                            parameter.Value = path;
                        }

                        command.CommandText += orderBy;
                        reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                    }

                    PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
                    long recordCount = 0;
                    long lBound = pageIndex * pageSize;
                    long uBound = lBound + pageSize;

                    while (reader.Read())
                    {
                        recordCount++;
                        if (recordCount <= lBound || recordCount > uBound)
                        {
                            continue;
                        }

                        string returnedPath = reader.GetString(0);
                        DateTime lastUpdatedDate = reader.GetDateTime(1);
                        int size = reader.GetInt32(2);

                        // Create temp info since we need to retrieve the corresponding personalization size and count later
                        stateInfoCollection.Add(new SharedPersonalizationStateInfo(returnedPath, lastUpdatedDate, size, -1, -1));
                    }
                    totalRecords = (int)recordCount;

                    // We need to close the reader in order to make other queries
                    reader.Close();
                    using (MySqlCommand command = new MySqlCommand(findUserState, connection))
                    {
                        parameters = command.Parameters;

                        parameter = parameters.Add("Path", MySqlDbType.VarChar);
                        PersonalizationStateInfoCollection sharedStateInfoCollection = new PersonalizationStateInfoCollection();

                        foreach (PersonalizationStateInfo stateInfo in stateInfoCollection)
                        {
                            parameter.Value = stateInfo.Path;

                            reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                            reader.Read();
                            int sizeOfPersonalizations = Convert.ToInt32(reader.GetValue(0), CultureInfo.InvariantCulture);
                            int countOfPersonalizations = reader.GetInt32(1);
                            reader.Close();
                            sharedStateInfoCollection.Add(new SharedPersonalizationStateInfo(
                                                                stateInfo.Path, stateInfo.LastUpdatedDate,
                                                                stateInfo.Size, sizeOfPersonalizations, countOfPersonalizations));
                        }

                        return sharedStateInfoCollection;
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }

                    //if (reader != null)
                    //{
                    //    reader.Close();
                    //    reader = null;
                    //}
                }
            }
            catch
            {
                throw;
            }
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Security", "CA2100:Review SQL queries for security vulnerabilities")]
        private PersonalizationStateInfoCollection FindUserState(string path, DateTime inactiveSinceDate, string username, int pageIndex, int pageSize, out int totalRecords)
        {
            const string findUserState =
                "SELECT Paths.Path, PerUser.LastUpdatedDate, LEN(PerUser.PageSettings), Users.UserName, Users.LastActivityDate" +
                " FROM AN_PagePersonalizationPerUser PerUser, AN_User Users, AN_Path Paths" +
                " WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId";
            const string orderBy = " ORDER BY Paths.Path ASC, Users.UserName ASC";

            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            MySqlDataReader reader = null;
            totalRecords = 0;


            try
            {
                try
                {
                    MySqlParameter parameter;
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.connectionInstance;
                    using (MySqlCommand command = new MySqlCommand())
                    {
                        command.Connection = connection;
                        MySqlParameterCollection parameters = command.Parameters;

                        command.CommandText = findUserState;
                        if (inactiveSinceDate != DateTime.MinValue)
                        {
                            command.CommandText += " AND Users.LastActivityDate <= @InactiveSinceDate";

                            // Note: MySql provider does not handle datetime that has non-
                            // zero millisecond, so it needs to be rounded up.
                            parameter = parameters.Add("InactiveSinceDate", MySqlDbType.DateTime);
                            parameter.Value = new DateTime(inactiveSinceDate.Year, inactiveSinceDate.Month, inactiveSinceDate.Day,
                                                           inactiveSinceDate.Hour, inactiveSinceDate.Minute, inactiveSinceDate.Second);
                        }

                        if (path != null)
                        {
                            command.CommandText += " AND Paths.Path LIKE @Path";
                            parameter = parameters.Add("Path", MySqlDbType.VarChar);
                            parameter.Value = path;
                        }

                        if (username != null)
                        {
                            command.CommandText += " AND Users.UserName LIKE @UserName";
                            parameter = parameters.Add("UserName", MySqlDbType.VarChar);
                            parameter.Value = username;
                        }

                        command.CommandText += orderBy;
                        reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                        PersonalizationStateInfoCollection stateInfoCollection = new PersonalizationStateInfoCollection();
                        long recordCount = 0;
                        long lBound = pageIndex * pageSize;
                        long uBound = lBound + pageSize;

                        while (reader.Read())
                        {
                            recordCount++;
                            if (recordCount <= lBound || recordCount > uBound)
                            {
                                continue;
                            }

                            string returnedPath = reader.GetString(0);
                            DateTime lastUpdatedDate = reader.GetDateTime(1);
                            int size = reader.GetInt32(2);
                            string returnedUsername = reader.GetString(3);
                            DateTime lastActivityDate = reader.GetDateTime(4);
                            stateInfoCollection.Add(new UserPersonalizationStateInfo(
                                                            returnedPath, lastUpdatedDate,
                                                            size, returnedUsername, lastActivityDate));
                        }
                        totalRecords = (int)recordCount;
                        return stateInfoCollection;
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }

                    if (reader != null)
                    {
                        reader.Close();
                    }
                }
            }
            catch
            {
                throw;
            }
        }

        private MySqlConnectionHolder GetConnectionHolder()
        {
            MySqlConnection connection = null;
            MySqlConnectionHolder connectionHolder = MySqlConnectionHelper.GetConnection(this.connectionString);

            if (connectionHolder != null)
            {
                connection = connectionHolder.connectionInstance;
            }
            if (connection == null)
            {
                throw new ProviderException("Personalization provider cannot access: " + Name);
            }

            return connectionHolder;
        }

        private int GetCountOfSharedState(string path)
        {
            string getSharedStateCount =
                "SELECT COUNT(*)" +
                " FROM AN_PagePersonalizationAllUsers AllUsers, AN_Path Paths" +
                " WHERE AllUsers.PathId = Paths.PathId";

            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            int count = 0;


            try
            {
                try
                {
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.connectionInstance;

                    using (MySqlCommand command = new MySqlCommand())
                    {
                        command.Connection = connection;
                        MySqlParameterCollection parameters = command.Parameters;

                        if (path != null)
                        {
                            getSharedStateCount += " AND Paths.Path LIKE @Path";
                            MySqlParameter parameter = parameters.Add("Path", MySqlDbType.VarChar);
                            parameter.Value = path;
                        }
                        command.CommandText = getSharedStateCount;

                        object result = command.ExecuteScalar();
                        if ((result != null) && (result is Int32))
                        {
                            count = (Int32)result;
                        }
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return count;
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1800:DoNotCastUnnecessarily")]
        private static string LoadPersonalizationBlob(MySqlConnection connection, int pathID)
        {
            using (MySqlCommand lookupCommand = new MySqlCommand("SELECT PageSettings FROM AN_PagePersonalizationAllUsers WHERE PathId = @PathId", connection))
            {
                lookupCommand.Parameters.Add(new MySqlParameter("@PathId", pathID));

                object lookupResult = lookupCommand.ExecuteScalar();
                if ((lookupResult != null) && (lookupResult is string))
                {
                    return (string)lookupResult;
                }
            }

            return null;
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Performance", "CA1800:DoNotCastUnnecessarily")]
        private static string LoadPersonalizationBlob(MySqlConnection connection, int pathID, int userID)
        {
            using (MySqlCommand updateCommand = new MySqlCommand(@"UPDATE  AN_User " +
                                                          @"SET     LastActivityDate = @LastActivityDate " +
                                                          @"WHERE   UserId = @UserId", connection))
            {
                updateCommand.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", DateTime.Now));
                updateCommand.Parameters.Add(new MySqlParameter("@UserId", userID));
                updateCommand.ExecuteNonQuery();
            }

            using (MySqlCommand lookupCommand = new MySqlCommand("SELECT PageSettings FROM AN_PagePersonalizationPerUser WHERE PathId = @PathId AND UserId = @UserId", connection))
            {
                lookupCommand.Parameters.Add(new MySqlParameter("@PathId", pathID));
                lookupCommand.Parameters.Add(new MySqlParameter("@UserId", userID));

                object lookupResult = lookupCommand.ExecuteScalar();

                if ((lookupResult != null) && (lookupResult is string))
                {
                    return (string)lookupResult;
                }
            }

            return null;
        }

        private int GetCountOfUserState(string path, DateTime inactiveSinceDate, string username)
        {
            string getUserStateCount =
                "SELECT COUNT(*)" +
                " FROM AN_PagePersonalizationPerUser PerUser, AN_User Users, AN_Path Paths" +
                " WHERE PerUser.UserId = Users.UserId AND PerUser.PathId = Paths.PathId";

            MySqlConnectionHolder connectionHolder = null;
            MySqlConnection connection = null;
            int count = 0;

            try
            {
                try
                {
                    MySqlParameter parameter;
                    connectionHolder = GetConnectionHolder();
                    connection = connectionHolder.connectionInstance;
                    using (MySqlCommand command = new MySqlCommand())
                    {
                        command.Connection = connection;
                        MySqlParameterCollection parameters = command.Parameters;

                        if (path != null)
                        {
                            getUserStateCount += " AND Paths.Path LIKE @Path";
                            parameter = parameters.Add("Path", MySqlDbType.VarChar);
                            parameter.Value = path;
                        }

                        if (username != null)
                        {
                            getUserStateCount += " AND LOWER(Users.UserName) LIKE LOWER(@UserName)";
                            parameter = parameters.Add("UserName", MySqlDbType.VarChar);
                            parameter.Value = username;
                        }

                        if (inactiveSinceDate != DateTime.MinValue)
                        {
                            getUserStateCount += " AND Users.LastActivityDate <= @InactiveSinceDate";

                            // Note: MySql provider does not handle datetime that has non-
                            // zero millisecond, so it needs to be rounded up.
                            parameter = parameters.Add("InactiveSinceDate", MySqlDbType.DateTime);
                            parameter.Value = new DateTime(inactiveSinceDate.Year, inactiveSinceDate.Month, inactiveSinceDate.Day,
                                                           inactiveSinceDate.Hour, inactiveSinceDate.Minute, inactiveSinceDate.Second);
                        }

                        command.CommandText = getUserStateCount;

                        object result = command.ExecuteScalar();
                        if ((result != null) && (result is Int32))
                        {
                            count = (Int32)result;
                        }
                    }
                }
                finally
                {
                    if (connectionHolder != null)
                    {
                        connectionHolder.Close();
                        connectionHolder = null;
                    }
                }
            }
            catch
            {
                throw;
            }

            return count;
        }
    }
}
